# coding:utf-8
from .MysqlPool import pool
from tornado import gen
import re
from tornado_mysql.util import byte2int


class Student:
    @classmethod
    def _check_class(cls, class_id: str):
        """"
            对传入的Class查询Class格式是否正确
            :param class_id :班级编号
            :return 格式正确则返回True
        """
        pattern = re.compile(r"^\d{7}$")
        if re.match(pattern, class_id):
            return True
        return ()

    @classmethod
    def _check_pid(cls, s_pid: str):
        """"
            对传入的S_PID 查询S_PID格式是否正确
            :param s_pid :身份证号
            :return 格式正确则返回True
        """
        pattern = r"(^\d{15}$)|(^\d{18}$)|(^\d{17}(\d|X|x)$)"
        if re.match(pattern, s_pid):
            return True
        return ()

    # ----------add----------
    @classmethod
    @gen.coroutine
    def add(cls, s_id, s_name, class_id, s_pid) -> bool:
        """"
            添加学生信息
            :param s_id :学号
            :param s_name:姓名
            :param class_id :班级（有格式要求）
            :param s_pid :身份证号（有格式要求）
            :return 格式正确则返回True
        """
        sql = "select count(s_id) as count from stu_info where s_id=%s"
        cursor = yield pool.execute(sql, (s_id,))
        data = cursor.fetchone()
        if data["count"] != 0:
            return False
        sql = "INSERT INTO stu_info (s_id,s_name,s_class,s_pid) VALUES (%s,%s,%s,%s)"
        parm = (s_id, s_name, class_id, s_pid)
        cursor = yield pool.execute(sql, parm)
        return cursor.rowcount != 0

    # ---------update---------
    @classmethod
    @gen.coroutine
    def update_face(cls, s_id, face_byte):
        """"
            将照片识别特征值通过学号加入该学生面部特征值
            :param face_byte:照片特征值, string
            :param s_id:学号
            :return 格式正确则返回True
        """
        cursor = yield pool.execute('UPDATE stu_info SET s_face =%s where s_id=%s', (face_byte, s_id))
        return cursor.rowcount != 0

    @classmethod
    @gen.coroutine
    def update_wechat(cls, s_id, wechat):
        """"
            将照片识别特征值通过学号加入该学生面部特征值
            :param wechat:微信识别码
            :param s_id:学号
            :return 格式正确则返回True
        """
        cursor = yield pool.execute('UPDATE stu_info SET s_face =%s where s_id=%s', (wechat, s_id))
        return cursor.rowcount != 0

    # ----------get----------
    @classmethod
    @gen.coroutine
    def get_count(cls) -> int:
        """"
            返回学生数量
            return 返回学生数量
        """
        cursor = yield pool.execute("select count(s_id) as count from stu_info")
        return cursor.fetchone()['count']

    @classmethod
    @gen.coroutine
    def get_by_id(cls, s_id) -> ():
        """"
            根据学号查找学生信息
            :param s_id :学号
            return 格式正确则返回该学生信息元组
        """
        sql = "select s_id as id,s_name as name,s_class as class, s_pid as pid,not isnull(s_face) as face_exist from stu_info where s_id = %s"
        cursor = yield pool.execute(sql, (s_id,))
        if cursor.rowcount == 0:
            return ()
        else:
            return cursor.fetchone()

    @classmethod
    @gen.coroutine
    def get_by_wechat(cls, s_wechat) -> ():
        """"
            根据微信授权码查找学生信息
            :param s_openid : 微信id
            :return 格式正确则返回该学生信息列表

        """
        cursor = yield pool.execute("select * from stu_info where s_wechat = %s", (s_wechat,))
        if cursor.rowcount == 0:
            return ()
        else:
            return cursor.fetchone()

    @classmethod
    @gen.coroutine
    def get_all(cls, page_num, num_in_page=10) -> ():
        """
            通过页号返回学生数据
            :param page_num: 页号,
            :param num_in_page: 一页多少数据
            :return: tuple
        """
        page_num = int(page_num) - 1
        sql = "select s_id as id,s_name as name,s_class as class, s_pid as pid from stu_info limit %s,%s"
        cursor = yield pool.execute(sql, (page_num * num_in_page, num_in_page))
        if cursor.rowcount == 0:
            return ()
        else:
            return cursor.fetchall()

    @classmethod
    @gen.coroutine
    def get_face_by_class(cls, s_class) -> ():
        """"
            通过班级查询所有学生学号
            :param s_class :班级
            :return 格式正确则返回所有学生face列表

        """
        cursor = yield pool.execute("select s_face from stu_info where s_class = %s", (s_class,))
        if cursor.rowcount == 0:
            return ()
        else:
            return cursor.fetchall()

    # ----------del----------
    @classmethod
    @gen.coroutine
    def del_by_id(cls, s_id):
        """"
            删除学生信息by s_id 返回是否成功
            :param s_id :学号
            :return 格式正确则返回True
        """
        cursor = yield pool.execute("delete from stu_info where s_id = %s", (s_id,))
        return cursor.rowcount != 0
